﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_InitAlertsOperators]
#-- Purpose:		Creates the default Alerts/Operators
#--	Last Update:	04/01/2016
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	[msdb].[dbo].[sp_add_operator]
#--					[master].[dbo].[sp_MSsetalertinfo]
#--					[msdb].[dbo].[sp_add_alert]
#--					[msdb].[dbo].[sp_update_alert]
#--					[msdb].[dbo].[sp_add_notification]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_InitAlertsOperators]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @iAlert INT, @sAlert nvarchar(255)
DECLARE @curr_date INT, @curr_time INT;

/*  Setup the Insufficient Resources Alert  */
DECLARE @sqlAdminName nvarchar(30), @email_address nvarchar(100)
SELECT	@sqlAdminName = '$(AdminEmailAccount)',
		@email_address = @sqlAdminName + '@$(AdminEmailDomainName)'

IF NOT EXISTS (SELECT name FROM [msdb].[dbo].[sysoperators] WHERE name = @sqlAdminName)
  BEGIN
	EXEC [msdb].[dbo].[sp_add_operator] @name=@sqlAdminName, @enabled=1, @pager_days=0, 
			@weekday_pager_start_time=90000, @weekday_pager_end_time=180000, 
			@saturday_pager_start_time=90000, @saturday_pager_end_time=180000, 
			@sunday_pager_start_time=90000, @sunday_pager_end_time=180000, 
			@email_address=@email_address, @category_name=N'[Uncategorized]'

	PRINT '   Added Operator [' + @sqlAdminName + ']'
  END

--- Set this operator as the Fail Safe Operator
EXEC [master].[dbo].[sp_MSsetalertinfo] @failsafeoperator=@sqlAdminName, @notificationmethod=1

SET @iAlert = 17
WHILE @iAlert <= 25
  BEGIN
	SET		@sAlert = (CASE	@iAlert
							WHEN 17 THEN 'Insufficient Resources'
							WHEN 18 THEN 'Nonfatal Internal Error'
							WHEN 19 THEN 'Fatal Error in Resource'
							WHEN 20 THEN 'Fatal Error in Current Process'
							WHEN 21 THEN 'Fatal Error in Database Processes'
							WHEN 22 THEN 'Fatal Error: Table Integrity Suspect'
							WHEN 23 THEN 'Fatal Error: Database Integrity Suspect'
							WHEN 24 THEN 'Fatal Error: Hardware Error'
							WHEN 25 THEN 'Fatal Error'
							ELSE NULL
							END)

	IF NOT EXISTS(SELECT TOP 1 name FROM [msdb].[dbo].[sysalerts] (NOLOCK) WHERE name = @sAlert)
	  BEGIN
		EXEC [msdb].[dbo].[sp_add_alert] @name=@sAlert, 
				@message_id=0, @severity=@iAlert, @enabled=1, 
				@delay_between_responses=900, @include_event_description_in=1, 
				@category_name=N'[Uncategorized]';

		SELECT	@curr_date = CONVERT(INT, CONVERT(CHAR, GETDATE(), 112)), 
				@curr_time = (DATEPART(hh, GETDATE()) * 10000) + (DATEPART(mi, GETDATE()) * 100) + (DATEPART(ss, GETDATE()));
		EXEC [msdb].[dbo].[sp_update_alert] @name = @sAlert, @count_reset_date = @curr_date, @count_reset_time = @curr_time, @occurrence_count = 0;

		PRINT '   Added Alert ' + QUOTENAME(@sAlert)
	  END

	IF EXISTS (SELECT name FROM [msdb].[dbo].[sysoperators] WHERE name = @sqlAdminName)
		AND EXISTS(SELECT TOP 1 name FROM [msdb].[dbo].[sysalerts] (NOLOCK) WHERE name = @sAlert)
		AND NOT EXISTS(SELECT TOP 1 alert_id FROM [msdb].[dbo].[sysnotifications] 
						WHERE alert_id = (SELECT id FROM [msdb].[dbo].[sysalerts] WHERE name = @sAlert) 
						AND operator_id = (SELECT id FROM [msdb].[dbo].[sysoperators] WHERE name = @sqlAdminName))
	  BEGIN
		EXEC [msdb].[dbo].[sp_add_notification] @alert_name=@sAlert, @operator_name=@sqlAdminName, @notification_method = 1;

		PRINT '   Added Operator [' + @sqlAdminName + '] to Alert ' + QUOTENAME(@sAlert)
	  END

	SET @iAlert = @iAlert + 1
  END

/*
Storage Alerts
http://www.sqlskills.com/blogs/paul/easy-monitoring-of-high-severity-errors-create-agent-alerts/
http://sqlmag.com/blog/sql-server-database-corruption-part-v-storage-problem-alerts
*/
SET @iAlert = 823
WHILE @iAlert <= 825
  BEGIN
	SET		@sAlert = (CASE	@iAlert
							WHEN 823 THEN 'Read/Write Failure (Msg #823)'
							WHEN 824 THEN 'Page Error (Msg #824)'
							WHEN 825 THEN 'Read-Retry Required (Msg #825)'
							ELSE NULL
							END)

	IF NOT EXISTS(SELECT TOP 1 name FROM [msdb].[dbo].[sysalerts] (NOLOCK) WHERE name = @sAlert)
	  BEGIN
		EXEC [msdb].[dbo].[sp_add_alert] @name=@sAlert, 
				@message_id=@iAlert, @severity=0, @enabled=1, 
				@delay_between_responses=900, @include_event_description_in=1, 
				@category_name=N'[Uncategorized]';

		SELECT	@curr_date = CONVERT(INT, CONVERT(CHAR, GETDATE(), 112)), 
				@curr_time = (DATEPART(hh, GETDATE()) * 10000) + (DATEPART(mi, GETDATE()) * 100) + (DATEPART(ss, GETDATE()));
		EXEC [msdb].[dbo].[sp_update_alert] @name = @sAlert, @count_reset_date = @curr_date, @count_reset_time = @curr_time, @occurrence_count = 0;

		PRINT '   Added Alert ' + QUOTENAME(@sAlert)
	  END

	IF EXISTS (SELECT name FROM [msdb].[dbo].[sysoperators] WHERE name = @sqlAdminName)
		AND EXISTS(SELECT TOP 1 name FROM [msdb].[dbo].[sysalerts] (NOLOCK) WHERE name = @sAlert)
		AND NOT EXISTS(SELECT TOP 1 alert_id FROM [msdb].[dbo].[sysnotifications] 
						WHERE alert_id = (SELECT id FROM [msdb].[dbo].[sysalerts] WHERE name = @sAlert) 
						AND operator_id = (SELECT id FROM [msdb].[dbo].[sysoperators] WHERE name = @sqlAdminName))
	  BEGIN
		EXEC [msdb].[dbo].[sp_add_notification] @alert_name=@sAlert, @operator_name=@sqlAdminName, @notification_method = 1;

		PRINT '   Added Operator [' + @sqlAdminName + '] to Alert ' + QUOTENAME(@sAlert)
	  END

	SET @iAlert = @iAlert + 1
  END

/*
AlwaysOn Availability Group Failover/Data Flow Alerts
http://sqlmag.com/blog/alwayson-ags-and-sql-server-jobs-setting-failover-alerts
https://msdn.microsoft.com/en-us/library/dn135324(v=sql.110).aspx  (adding 35201 based on occurence this week)
*/
SET @iAlert = 1480
WHILE ISNULL(@iAlert, -1) > -1
  BEGIN
	SET		@sAlert = (CASE	@iAlert
							WHEN 1480 THEN 'Availability Group Role Change (Msg #1480)'
							WHEN 35201 THEN 'Availability Group Data Movement Suspended (Msg #35201)'
							WHEN 35264 THEN 'Availability Group Data Movement Suspended (Msg #35264)'
							WHEN 35265 THEN 'Availability Group Data Movement Resumed (Msg #35265)'
							ELSE NULL
							END)

	IF NOT EXISTS(SELECT TOP 1 name FROM [msdb].[dbo].[sysalerts] (NOLOCK) WHERE name = @sAlert)
		AND EXISTS (SELECT TOP 1 [message_id] FROM [sys].[messages] WHERE [message_id] = @iAlert)
	  BEGIN
		--- No delay on these alerts; tell us right away if failover is occurring
		EXEC [msdb].[dbo].[sp_add_alert] @name=@sAlert, 
				@message_id=@iAlert, @severity=0, @enabled=1, 
				@delay_between_responses=0, @include_event_description_in=1, 
				@category_name=N'[Uncategorized]';

		SELECT	@curr_date = CONVERT(INT, CONVERT(CHAR, GETDATE(), 112)), 
				@curr_time = (DATEPART(hh, GETDATE()) * 10000) + (DATEPART(mi, GETDATE()) * 100) + (DATEPART(ss, GETDATE()));
		EXEC [msdb].[dbo].[sp_update_alert] @name = @sAlert, @count_reset_date = @curr_date, @count_reset_time = @curr_time, @occurrence_count = 0;

		PRINT '   Added Alert ' + QUOTENAME(@sAlert)
	  END

	IF EXISTS (SELECT name FROM [msdb].[dbo].[sysoperators] WHERE name = @sqlAdminName)
		AND EXISTS(SELECT TOP 1 name FROM [msdb].[dbo].[sysalerts] (NOLOCK) WHERE name = @sAlert)
		AND NOT EXISTS(SELECT TOP 1 alert_id FROM [msdb].[dbo].[sysnotifications] 
						WHERE alert_id = (SELECT id FROM [msdb].[dbo].[sysalerts] WHERE name = @sAlert) 
						AND operator_id = (SELECT id FROM [msdb].[dbo].[sysoperators] WHERE name = @sqlAdminName))
	  BEGIN
		EXEC [msdb].[dbo].[sp_add_notification] @alert_name=@sAlert, @operator_name=@sqlAdminName, @notification_method = 1;

		PRINT '   Added Operator [' + @sqlAdminName + '] to Alert ' + QUOTENAME(@sAlert)
	  END

	SET @iAlert = (CASE @iAlert
						WHEN 1480 THEN 35201
						WHEN 35201 THEN 35264
						WHEN 35264 THEN 35265
						ELSE NULL
						END)
  END

SET NOCOUNT OFF